package com.gzgy.ustore.api.common.util;

import java.beans.BeanInfo;
import java.beans.IntrospectionException;
import java.beans.Introspector;
import java.beans.PropertyDescriptor;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.Serializable;
import java.lang.reflect.InvocationTargetException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;

public class ExportExcelUtil<T>{
	
	//private Log logger = LogFactory.getLog(this.getClass());
	
	protected HSSFCellStyle getHeaderStyle(HSSFWorkbook workbook) {
		HSSFCellStyle style = workbook.createCellStyle();
		style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
		style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);
		style.setBorderTop(HSSFCellStyle.BORDER_THIN);
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		style.setWrapText(true);

		HSSFFont font = workbook.createFont();
		font.setFontHeightInPoints((short) 11);
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		font.setFontName("Arial");
		style.setFont(font);
		return style;
	}

	protected HSSFCellStyle getCellStyle(HSSFWorkbook workbook) {
		HSSFCellStyle style2 = workbook.createCellStyle();
		style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
		style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
		style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		style2.setAlignment(HSSFCellStyle.VERTICAL_CENTER);
		style2.setWrapText(true);
		HSSFFont font2 = workbook.createFont();
		font2.setFontHeightInPoints((short) 11);
		font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
		font2.setFontName("Arial");
		style2.setFont(font2);
		return style2;
	}
	
	
	protected CellStyle getHeaderStyle2007(Workbook workbook) {
		CellStyle style = workbook.createCellStyle();
		style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
		style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);
		style.setBorderTop(HSSFCellStyle.BORDER_THIN);
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		style.setWrapText(true);

		Font font = workbook.createFont();
		font.setFontHeightInPoints((short) 11);
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		font.setFontName("Arial");
		style.setFont(font);
		return style;
	}

	protected CellStyle getCellStyle2007(Workbook workbook) {
		CellStyle style2 = workbook.createCellStyle();
		style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
		style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
		style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		style2.setAlignment(HSSFCellStyle.VERTICAL_CENTER);
		style2.setWrapText(true);
		Font font2 = workbook.createFont();
		font2.setFontHeightInPoints((short) 11);
		font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
		font2.setFontName("Arial");
		style2.setFont(font2);
		return style2;
	}
	/**
	 * 使用導出  *.xlsx 適用大數據
	 * @param title
	 * @param headers
	 * @param dataset
	 * @param pattern
	 * @param path
	 * @throws IOException
	 */
	public void exportExcel2007(String title, String[] headers,
			Collection<T> dataset, String dateFormat, String path,String fileName)
			throws IOException {
		
		exportExcel2007(title,headers,dataset,dateFormat,path,fileName,true);
	}
	/**
	 * 使用導出  *.xlsx 適用大數據
	 * @param title
	 * @param headers
	 * @param dataset
	 * @param pattern
	 * @param path
	 * @param showTitle
	 * @throws IOException
	 * @throws  
	 */
	public void exportExcel2007(String title, String[] headers,
			Collection<T> dataset, String dateFormat, String path,String fileName,boolean showTitle) throws IOException  {
		FileOutputStream fos = null;
		Workbook workbook = null ;
		try {
			fos = new FileOutputStream(new File(path, fileName+".xlsx"));
			workbook = new SXSSFWorkbook(100); // keep 100 rows in memory, exceeding rows will be flushed to disk 
			Sheet sheet = workbook.createSheet(title);  
			sheet.setDefaultColumnWidth(25);
			sheet.createFreezePane(0, 2, 0, 2);
			// 声明一个画图的顶级管理器
			Drawing drawing = sheet.createDrawingPatriarch();
			if(showTitle) {
				// create header
				Row row = sheet.createRow(1);
				for (int i = 0; i < headers.length; i++) {
					Cell cell = row.createCell(i);
					cell.setCellStyle(getHeaderStyle2007(workbook));
					XSSFRichTextString text = new XSSFRichTextString(headers[i]);
					cell.setCellValue(text);
				}
			}	
			// create row and cell
			Iterator<T> it = dataset.iterator();
			int index = showTitle ? 1 : -1 ;
			CellStyle style = getCellStyle2007(workbook);
			while (it.hasNext()) {
				index++;
				Row row = sheet.createRow(index);
				T t = (T) it.next();
				int y=0;
				
			    BeanInfo beanInfo = Introspector.getBeanInfo(t.getClass());  
			    PropertyDescriptor[] propertyDescriptors = beanInfo.getPropertyDescriptors();  
				for(int i=0;i<propertyDescriptors.length;i++){
					if(!propertyDescriptors[i].getName().equals("class")) {
				    //String lsName = propertyDescriptors[i].getName();   // 属性
					Object value = propertyDescriptors[i].getReadMethod().invoke(t, new Object[]{});  // 值
				    //String lsSourceType = propertyDescriptors[i].getPropertyType().getName(); //类型
				    
				    Cell cell = row.createCell(y);
					cell.setCellStyle(style);
						
					y++;
					// 判断值的类型后进行强制类型转换
					String textValue = null;
					// if (value instanceof Integer) {
					// int intValue = (Integer) value;
					// cell.setCellValue(intValue);
					// } else if (value instanceof Float) {
					// float fValue = (Float) value;
					// textValue = new HSSFRichTextString(
					// String.valueOf(fValue));
					// cell.setCellValue(textValue);
					// } else if (value instanceof Double) {
					// double dValue = (Double) value;
					// textValue = new HSSFRichTextString(
					// String.valueOf(dValue));
					// cell.setCellValue(textValue);
					// } else if (value instanceof Long) {
					// long longValue = (Long) value;
					// cell.setCellValue(longValue);
					// }
					if (value instanceof Boolean) {
						boolean bValue = (Boolean) value;
						textValue=bValue?"true":"false";
					}else if (value instanceof Date) {
						Date date = (Date) value;
						SimpleDateFormat sdf = new SimpleDateFormat(dateFormat);
						textValue = sdf.format(date);
					}else if (value instanceof byte[]) {
						// 有图片时，设置行高为60px;
						row.setHeightInPoints(60);
						// 设置图片所在列宽度为80px,注意这里单位的一个换算
						sheet.setColumnWidth(i, (short) (35.7 * 80));
						// sheet.autoSizeColumn(i);
						byte[] bsValue = (byte[]) value;
						XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0,1023, 255, (short) 6, index, (short) 6, index);
						anchor.setAnchorType(2);
						drawing.createPicture(anchor, workbook.addPicture(bsValue, SXSSFWorkbook.PICTURE_TYPE_JPEG));
					}else {
						// 其它数据类型都当作字符串简单处理
						textValue = value.toString();
					}
					
					if (textValue != null) {
						Pattern p = Pattern.compile("^//d+(//.//d+)?$");
						Matcher matcher = p.matcher(textValue);
						if (matcher.matches()) {
						// 是数字当作double处理
							cell.setCellValue(Double.parseDouble(textValue));
						} else {
							XSSFRichTextString richString = new XSSFRichTextString(
									textValue);
							cell.setCellValue(richString);
						}
					}
		
				   }
				}
				
			}
		//写入excel
		workbook.write(fos);
		}catch (SecurityException e){
			e.printStackTrace();
		}catch (IntrospectionException e){
			e.printStackTrace();
		}catch (IllegalArgumentException e){
			e.printStackTrace();
		}catch (IllegalAccessException e){
			e.printStackTrace();
		}catch (InvocationTargetException e){
			e.printStackTrace();
		}catch (IOException e){
			e.printStackTrace();
		}finally {
			if (fos != null)
				fos.close();
		}			
	}
	
	/**
	 * export 低版本的excel
	 * @param title
	 * @param headers
	 * @param dataset
	 * @param pattern
	 * @param path
	 * @throws IOException
	 */
	public void exportExcel(String title, String[] headers,
			Collection<T> dataset, String dateFormat,String path,String fileName)
			throws IOException {
		exportExcel(title, headers, dataset, dateFormat,path,fileName,true);
	}
	
	public void exportExcel(String title, String[] headers,
			Collection<T> dataset, String dateFormat, String path,String fileName,boolean showTitle)
			throws IOException {
		FileOutputStream fos =null; 
		HSSFWorkbook workbook =null;
		try {	
			fos =new FileOutputStream(new File(path, fileName+".xls"));
			workbook=new HSSFWorkbook();
			HSSFSheet sheet = workbook.createSheet(title);
			sheet.setDefaultColumnWidth(25);
			sheet.createFreezePane(0, 2, 0, 2);
			// 声明一个画图的顶级管理器
			HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
			
			if(showTitle) {
				// create header
				HSSFRow row = sheet.createRow(1);
				for (int i = 0; i < headers.length; i++) {
					HSSFCell cell = row.createCell(i);
					cell.setCellStyle(getHeaderStyle(workbook));
					HSSFRichTextString text = new HSSFRichTextString(headers[i]);
					cell.setCellValue(text);
				}
			}
			// create row and cell
			Iterator<T> it = dataset.iterator();
			int index = showTitle ? 1 : -1 ;
			HSSFCellStyle style = getCellStyle(workbook);
			while (it.hasNext()) {
				index++;
				HSSFRow row = sheet.createRow(index);
				T t = (T) it.next();
				int y=0;
				
			    BeanInfo beanInfo = Introspector.getBeanInfo(t.getClass());  
			    PropertyDescriptor[] propertyDescriptors = beanInfo.getPropertyDescriptors();  
				for(int i=0;i<propertyDescriptors.length;i++){
					if(!propertyDescriptors[i].getName().equals("class")) {
				    //String lsName = propertyDescriptors[i].getName();   // 属性
					Object value = propertyDescriptors[i].getReadMethod().invoke(t, new Object[]{});  // 值
				    //String lsSourceType = propertyDescriptors[i].getPropertyType().getName(); //类型
				    
				    Cell cell = row.createCell(y);
					cell.setCellStyle(style);
						
					y++;
					// 判断值的类型后进行强制类型转换
					String textValue = null;
					// if (value instanceof Integer) {
					// int intValue = (Integer) value;
					// cell.setCellValue(intValue);
					// } else if (value instanceof Float) {
					// float fValue = (Float) value;
					// textValue = new HSSFRichTextString(
					// String.valueOf(fValue));
					// cell.setCellValue(textValue);
					// } else if (value instanceof Double) {
					// double dValue = (Double) value;
					// textValue = new HSSFRichTextString(
					// String.valueOf(dValue));
					// cell.setCellValue(textValue);
					// } else if (value instanceof Long) {
					// long longValue = (Long) value;
					// cell.setCellValue(longValue);
					// }
					if (value instanceof Boolean) {
						boolean bValue = (Boolean) value;
						textValue=bValue?"true":"false";
					}else if (value instanceof Date) {
						Date date = (Date) value;
						SimpleDateFormat sdf = new SimpleDateFormat(dateFormat);
						textValue = sdf.format(date);
					}else if (value instanceof byte[]) {
						// 有图片时，设置行高为60px;
						row.setHeightInPoints(60);
						// 设置图片所在列宽度为80px,注意这里单位的一个换算
						sheet.setColumnWidth(i, (short) (35.7 * 80));
						// sheet.autoSizeColumn(i);
						byte[] bsValue = (byte[]) value;
						HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0,1023, 255, (short) 6, index, (short) 6, index);
						anchor.setAnchorType(2);
						patriarch.createPicture(anchor, workbook.addPicture(bsValue, HSSFWorkbook.PICTURE_TYPE_JPEG));
					}else {
						// 其它数据类型都当作字符串简单处理
						textValue = value.toString();
					}
					
					if (textValue != null) {
						Pattern p = Pattern.compile("^//d+(//.//d+)?$");
						Matcher matcher = p.matcher(textValue);
						if (matcher.matches()) {
						// 是数字当作double处理
							cell.setCellValue(Double.parseDouble(textValue));
						} else {
							HSSFRichTextString richString = new HSSFRichTextString(
									textValue);
							HSSFFont font3 = workbook.createFont();
							font3.setColor(HSSFColor.BLUE.index);
							richString.applyFont(font3);
							cell.setCellValue(richString);
						}
					}
		
				   }
				}
				
			}
		//写入excel
		workbook.write(fos);
		}catch (SecurityException e){
			e.printStackTrace();
		}catch (IntrospectionException e){
			e.printStackTrace();
		}catch (IllegalArgumentException e){
			e.printStackTrace();
		}catch (IllegalAccessException e){
			e.printStackTrace();
		}catch (InvocationTargetException e){
			e.printStackTrace();
		}catch (IOException e){
			e.printStackTrace();
		}finally {
			if (fos != null)
				fos.close();
		
		}
	}
	
	public static void main(String[] args)
	{
		// 测试学生
		ExportExcelUtil<TestExcel> ex1 = new ExportExcelUtil<TestExcel>();
		String[] headers =
		{ "学号", "姓名",};
		List<TestExcel> dataset = new ArrayList<TestExcel>();
		dataset.add(new TestExcel("10000001", "张三"));
		dataset.add(new TestExcel("20000002", "李四"));
		
		try
		{	
			ex1.exportExcel2007("435", headers, dataset, "yyyy-mm-dd", "E://","aaa");
		}
		catch (FileNotFoundException e)
		{
			e.printStackTrace();
		}
		catch (IOException e)
		{
			e.printStackTrace();
		}
	}
}

class TestExcel implements Serializable{
	
	 /**
	 * 
	 */
	private static final long serialVersionUID = 3529440005271651366L;
	private String aaa;
	private String bbb;



	public TestExcel(String aaa,String bbb){
		this.setAaa(aaa);
		this.setBbb(bbb);
	}
	public String getAaa() {
		return aaa;
	}
	public void setAaa(String aaa) {
		this.aaa = aaa;
	}
	public String getBbb() {
		return bbb;
	}
	public void setBbb(String bbb) {
		this.bbb = bbb;
	}
}